--EXEC sp_update_backlog_for_shipped_lineitem 2109

ALTER PROC sp_update_backlog_for_shipped_lineitem  
@cusotmerShippingId INT
AS
BEGIN
	DECLARE @salesOrderId INT
	DECLARE @lineItemNo INT
	DECLARE @qtyShipped INT
	DECLARE @qtyAllocated INT
	DECLARE @currQuantity INT
	DECLARE @lineItemCursor CURSOR 
	DECLARE @productId INT
	DECLARE @reserveLotCursor CURSOR
	DECLARE @Lot_Id int
	DECLARE @lotQty_Allocated int
	DECLARE @totalLotQty int
	
	SET @lineItemCursor = CURSOR FOR SELECT SalesOrder_ID, COOL_Line_Item_No, CSLIT_Qty_Shipped, Product_ID FROM Customer_Open_Order_Lineitem COOL, Customer_Shipping_Line_Item_Table CSLIT WHERE COOL.LineItem_ID = CSLIT.LineItem_ID AND CustomerShipping_ID = @cusotmerShippingId
	OPEN @lineItemCursor
	FETCH NEXT FROM @lineItemCursor INTO @salesOrderId, @lineItemNo, @qtyShipped, @productId
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @qtyAllocated = Qty_Allocated FROM Backlog_Master WHERE SalesOrder_Id = @salesOrderId AND LineItem_No = @lineItemNo

		IF @qtyAllocated IS NOT NULL
		BEGIN
			DECLARE @fgProcessStepId INT 
			DECLARE @parentProductId INT
			DECLARE @pType VARCHAR(50)

			SET @fgProcessStepId = -1
			SELECT @fgProcessStepId = processstep_id, @pType = P_Type, @parentProductId = P_Parent_Product_ID FROM product WHERE Product_Id = @productId
	
			IF( @pType <> 'CHIP' )
			BEGIN
			 	IF( @parentProductId != -1)
		 		BEGIN	
		 			SELECT @fgProcessStepId = processStep_ID FROM product WHERE Product_id = @parentProductId
			 	END
		 	END
			
			SET @totalLotQty = 0

			SET @reserveLotCursor = CURSOR FOR SELECT Lot_Id, Qty_Allocated FROM Wip_Reservation_Table WHERE SalesOrder_Id = @salesOrderId and Line_Item_No = @lineItemNo 
			OPEN @reserveLotCursor
			FETCH NEXT FROM @reserveLotCursor INTO @Lot_Id, @lotQty_Allocated
			WHILE @@FETCH_STATUS = 0
			BEGIN
				--PRINT 'LOT ID '+ CONVERT( char, @Lot_Id )+ ' Qty Allocated ' + CONVERT( CHAR, @lotQty_Allocated ) + ' Process Step id  '+ convert( char, @fgProcessStepId )
				IF  @totalLotQty + @lotQty_Allocated <= @qtyShipped
				BEGIN
					UPDATE Wip_Master SET Qty_Allocated = Qty_Allocated - ( ( ( Fg_Qty * @lotQty_Allocated ) / ( select Fg_Qty from Wip_Master where Fg_processStep_Id = @fgProcessStepId and lot_Id = @Lot_Id ) ) ) where lot_Id = @Lot_Id
					DELETE FROM Wip_Reservation_Table WHERE SalesOrder_Id = @salesOrderId AND Line_Item_No = @lineItemNo and Lot_Id = @Lot_Id
					SET @totalLotQty = @totalLotQty + @lotQty_Allocated
				END
				ELSE
				BEGIN
					SET @lotQty_Allocated = @qtyShipped - @totalLotQty
					--PRINT 'WHEN LESS '+ CONVERT( CHAR, @lotQty_Allocated )
					UPDATE Wip_Master SET Qty_Allocated = Qty_Allocated - ( ( ( Fg_Qty * @lotQty_Allocated ) / ( select Fg_Qty from Wip_Master where Fg_processStep_Id = @fgProcessStepId and lot_Id = @Lot_Id ) ) ) where lot_Id = @Lot_Id
					UPDATE Wip_Reservation_Table SET Qty_Allocated = Qty_Allocated - @lotQty_Allocated WHERE SalesOrder_Id = @salesOrderId AND Line_Item_No = @lineItemNo and Lot_Id = @Lot_Id
					SET @totalLotQty = @qtyShipped
				END
				
				FETCH NEXT FROM @reserveLotCursor INTO @Lot_Id, @lotQty_Allocated
			END
			CLOSE @reserveLotCursor
			DEALLOCATE @reserveLotCursor

			
			IF ( @qtyAllocated <= @qtyShipped )
			BEGIN
				DELETE FROM Backlog_Master WHERE SalesOrder_Id = @salesOrderId AND LineItem_No = @lineItemNo
				--DELETE FROM Wip_Reservation_Table WHERE SalesOrder_Id = @salesOrderId AND Line_Item_No = @lineItemNo
			END
			ELSE
			BEGIN
				UPDATE Backlog_Master SET Qty_Allocated = Qty_Allocated - @qtyShipped WHERE SalesOrder_Id = @salesOrderId AND LineItem_No = @lineItemNo
				--DELETE FROM Wip_Reservation_Table WHERE SalesOrder_Id = @salesOrderId AND Line_Item_No = @lineItemNo
			END

		END
		ELSE
		BEGIN
			UPDATE Backlog_Master SET Qty_Allocated = @qtyAllocated where SalesOrder_Id = @salesOrderId AND LineItem_No = @lineItemNo
		END
		FETCH NEXT FROM @lineItemCursor INTO @salesOrderId, @lineItemNo, @qtyShipped, @productId
	END
END